A materialized view (MV) is a query result stored in tabular format in a relational database management system (RDBMS). It is used to pre-calculate joins and/or aggregates so that the results can be fetched directly to answer a query when applicable instead of calculating from the base table. It can significantly improve the query performance in a large decision support system (DSS).
Materialized views may be used in a query plan through query rewrite which is transparent to users. A query optimizer determines whether one or more MVs will be used to answer a query by replacing the corresponding base table(s). In this process, the optimizer first evaluates if a MV is applicable, such as if it has all the columns selected in the query, if it contains a superset of rows requested by the query, etc. Then the optimizer decides whether an applicable MV is actually used based on certain criteria. When there are multiple MVs, the number of ways to rewrite a query with different combinations of MVs can be large. The choice of MVs is partially rule-based due to the complexity of the search space. The situation becomes worse when there are aggregates in the MV definition, which may be referred to as an aggregate MV (AMV). In some cases the choice of AMVs is completely rule-based, which may result in suboptimal plans in query rewrite.